package com.xff.dao;


import com.xff.bean.ApplyBean;
import com.xff.bean.HistoryBean;
import com.xff.bean.SubscribeBean;
import com.xff.dbUtils.DbUtil;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

public class SubscribeDao {

    /*
	* 添加预约的记录
	*
	* */
    public void addSubBook(SubscribeBean subscribeBean) {
        //连接数据库
        Connection conn = DbUtil.getConn();
        //SQL
        String sql = "insert into subscribe(aid,bid,card,bookname,username,name,subtime) values(?,?,?,?,?,?,?)";
        int rs = 0;
        PreparedStatement stm = null;
        try {
            //预编译SQL，减少sql执行
            stm = conn.prepareStatement(sql);
            //添加
            stm.setInt(1,subscribeBean.getAid());
            stm.setInt(2,subscribeBean.getBid());
            stm.setString(3, subscribeBean.getCard());
            stm.setString(4,subscribeBean.getBookname());
            stm.setString(5,subscribeBean.getUsername());
            stm.setString(6,subscribeBean.getName());

            SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date=format.format(new java.util.Date());
            stm.setString(7,date);

            //执行更新
            rs = stm.executeUpdate();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
    /*
	* 预约查询
	* */
    public ArrayList<SubscribeBean> get_SubscribeListInfo(){
        ArrayList<SubscribeBean> tag_Array = new ArrayList<SubscribeBean>();
        Connection conn = DbUtil.getConn();
        String sql = "select * from subscribe ";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                SubscribeBean tag = new SubscribeBean();
                tag.setSubscribeid(rs.getInt("subscribeid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setSubtime(rs.getDate("subtime"));
                tag_Array.add(tag);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    /**
     * 获取借阅记录，传入的条件有name，tip，表示搜索相关图书名称和读者账号/姓名的记录
     *
     */
    public ArrayList<SubscribeBean> get_SubscribeListInfo(int tip, String name){
        ArrayList<SubscribeBean> tag_Array = new ArrayList<SubscribeBean>();
        Connection conn = DbUtil.getConn();
        String sql="select * from subscribe";
        if(tip==1)//根据书名查询
            sql = "select * from subscribe where bookname like '%"+ name +"%'";
        if(tip==2)//根据用户查询
            sql = "select * from subscribe where name like '%"+ name +"%' or username like '%"+name+"%'";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                SubscribeBean tag = new SubscribeBean();
                tag.setSubscribeid(rs.getInt("subscribeid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setSubtime(rs.getDate("subtime"));
                tag_Array.add(tag);

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    /*
	* 预约热度顺序排名查询
	* */
    public ArrayList<SubscribeBean> get_SubscribeRank(int tip){
        ArrayList<SubscribeBean> tag_Array = new ArrayList<SubscribeBean>();
        Connection conn = DbUtil.getConn();
        String sql = "select * from subscribe ";
        if(tip==0){//降序
            sql = "SELECT card,bookname,COUNT(*) as number FROM subscribe GROUP BY bookname ORDER BY number DESC ";
        }else {//升序
            sql = "SELECT card,bookname,COUNT(*) as number FROM subscribe GROUP BY bookname ORDER BY number ";
        }
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                SubscribeBean tag = new SubscribeBean();
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setSubscribenum(rs.getInt("number"));
                tag_Array.add(tag);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

   public ArrayList<SubscribeBean> get_SubscribeListInfo(String username){
       ArrayList<SubscribeBean> tag_Array = new ArrayList<SubscribeBean>();
       HashMap<String,Integer> sub=get_subscribenum();

       Connection conn = DbUtil.getConn();
       String sql="select * from subscribe where username='"+username+"'";
       BookDao bookDao=new BookDao();
       PreparedStatement stm = null;
       ResultSet rs = null;
       try {
           stm = conn.prepareStatement(sql);
           rs = stm.executeQuery();
           while (rs.next()) {
               SubscribeBean tag = new SubscribeBean();
               tag.setSubscribeid(rs.getInt("subscribeid"));
               tag.setAid(rs.getInt("aid"));
               tag.setBid(rs.getInt("bid"));
               tag.setBookname(rs.getString("bookname"));
               tag.setCard(rs.getString("card"));
               tag.setUsername(rs.getString("username"));
               tag.setName(rs.getString("name"));
               tag.setSubtime(rs.getDate("subtime"));
               tag.setLendable_num(bookDao.get_BookInfo(tag.getBid()).getSubscribenum());
               tag.setSubscribenum(sub.get(tag.getBookname()));
               tag_Array.add(tag);
           }
       } catch (Exception e) {
           // TODO: handle exception
           e.printStackTrace();
       }
       finally {
           DbUtil.CloseDB(rs, stm, conn);
       }
       return tag_Array;
   }

    public ArrayList<SubscribeBean> get_SubscribeListInfo(String username,int bid){
        ArrayList<SubscribeBean> tag_Array = new ArrayList<SubscribeBean>();
        HashMap<String,Integer> sub=get_subscribenum();

        Connection conn = DbUtil.getConn();
        String sql="select * from subscribe where username='"+username+"' AND bid="+bid;
        BookDao bookDao=new BookDao();
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                SubscribeBean tag = new SubscribeBean();
                tag.setSubscribeid(rs.getInt("subscribeid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setSubtime(rs.getDate("subtime"));
                tag.setLendable_num(bookDao.get_BookInfo(tag.getBid()).getSubscribenum());
                tag.setSubscribenum(sub.get(tag.getBookname()));
                tag_Array.add(tag);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

   public void deleteSubscribe(int subscribeid){
       Connection conn = DbUtil.getConn();
       String sql = "delete from subscribe where subscribeid=?";
       PreparedStatement stm = null;
       try {
           stm = conn.prepareStatement(sql);
           stm.setInt(1, subscribeid);
           stm.executeUpdate();
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   }

    public HashMap<String,Integer> get_subscribenum(){
        HashMap<String,Integer> sub=new HashMap<>();
        Connection conn = DbUtil.getConn();
        String sql = "SELECT bookname,COUNT(*) as number FROM subscribe GROUP BY bookname ";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                sub.put(rs.getString("bookname"),rs.getInt("number"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return sub;
    }

}
